package com.wenda.homeschooedu.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wenda.homeschooedu.DB.DB.Table;
import com.wenda.homeschooedu.DB.JDBCConnection;
import com.wenda.homeschooedu.dao.IHomework;
import com.wenda.homeschooedu.model.HomeWork;

public class HomeWorkService implements IHomework {
	JDBCConnection jdbcConnection;
	Statement statement;

	public HomeWorkService() {
		jdbcConnection = new JDBCConnection();
		statement = jdbcConnection.getStm();
	}

	public List<HomeWork> getAllHomeWorksByClassId(int class_id) {
		List<HomeWork> homeWorks = new ArrayList<HomeWork>();
		String sql = String.format(Table.Homework.SQL.SELECT, "class_id = "+class_id);
		try {
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet != null) {
				while (resultSet.next()) {
					HomeWork homeWork = new HomeWork();
					homeWork.setClass_name(resultSet.getString(resultSet
							.findColumn("class_name")));
					homeWork.setContent(resultSet.getString(resultSet
							.findColumn("thecontent")));
					homeWork.setHomework_id(resultSet.getInt(resultSet
							.findColumn("_id")));
					homeWork.setSubject_id(resultSet.getInt(resultSet
							.findColumn("subject_id")));
					homeWork.setSubject_name(resultSet.getString(resultSet
							.findColumn("subject_name")));
					homeWork.setTime(resultSet.getString(resultSet
							.findColumn("thetime")));
					homeWorks.add(homeWork);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return homeWorks;
	}

	public List<HomeWork> getAllHomeWorksByStudentId(int student_id) {
		int class_id = 0;
		List<HomeWork> homeWorks = new ArrayList<HomeWork>();
		String sql = String.format(
				Table.Student.SQL.SELECT_CLASSID_BY_STUDENTID, student_id);
		try {
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet != null) {
				resultSet.next();
				class_id = resultSet.getInt(resultSet.findColumn("class_id"));
				homeWorks = this.getAllHomeWorksByClassId(class_id);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return homeWorks;
	}

	public List<HomeWork> getNewHomeWorksByStudentId(int student_id,
			int old_homework_id) {
		int class_id = 0;
		List<HomeWork> homeWorks = new ArrayList<HomeWork>();
		String sql = String.format(
				Table.Student.SQL.SELECT_CLASSID_BY_STUDENTID, student_id);
		try {
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet != null) {
				resultSet.next();
				class_id = resultSet.getInt(resultSet.findColumn("class_id"));
				homeWorks = this.getNewHomeWorksByClassId(class_id , old_homework_id);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return homeWorks;
		
	}

	public List<HomeWork> getNewHomeWorksByClassId(int class_id,
			int old_homework_id) {
		List<HomeWork> homeWorks = new ArrayList<HomeWork>();
		String sql = String.format(Table.Homework.SQL.SELECT, "class_id = "+class_id + " and  homework._id >" + old_homework_id);
		try {
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet != null) {
				while (resultSet.next()) {
					HomeWork homeWork = new HomeWork();
					homeWork.setClass_name(resultSet.getString(resultSet
							.findColumn("class_name")));
					homeWork.setContent(resultSet.getString(resultSet
							.findColumn("thecontent")));
					homeWork.setHomework_id(resultSet.getInt(resultSet
							.findColumn("_id")));
					homeWork.setSubject_id(resultSet.getInt(resultSet
							.findColumn("subject_id")));
					homeWork.setSubject_name(resultSet.getString(resultSet
							.findColumn("subjectname")));
					homeWork.setTime(resultSet.getString(resultSet
							.findColumn("thetime")));
					homeWorks.add(homeWork);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return homeWorks;
	}

}
